package com.muk.meis.model.service.report;

import java.io.StringWriter;
import java.util.EnumSet;
import java.util.Iterator;
import java.util.List;

import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
import javax.xml.transform.Transformer;
import javax.xml.transform.TransformerFactory;
import javax.xml.transform.dom.DOMSource;
import javax.xml.transform.stream.StreamResult;

import org.w3c.dom.Document;
import org.w3c.dom.Element;
import org.w3c.dom.Text;

import com.muk.meis.MEISConstance.DISTRICT;
import com.muk.meis.MEISConstance.SCALE;
import com.muk.meis.model.service.BaseMEISService;

public class ReportByScale extends BaseMEISService {

	public Integer[] getByScale(SCALE scale, DISTRICT district){
		
		String sql = "select b.percodeCode ,"+
					 " sum(a.kindergartenMan3+a.kindergartenMan2+a.kindergartenMan1+a.primaryEducationMan1+"+
					 " a.primaryEducationMan2+a.primaryEducationMan3+a.primaryEducationMan4+a.primaryEducationMan5+a.primaryEducationMan6+"+
					 " a.highSchoolMan1+a.highSchoolMan2+a.highSchoolMan3+a.highSchoolMan4+a.highSchoolMan5+a.highSchoolMan6),"+
					 "	sum(a.kindergartenGirl3+a.kindergartenGirl2+a.kindergartenGirl1+"+
					 "	a.primaryEducationGirl1+a.primaryEducationGirl2+a.primaryEducationGirl3+a.primaryEducationGirl4+a.primaryEducationGirl5+a.primaryEducationGirl6+"+
					 "	a.highSchoolGirl1+a.highSchoolGirl2+a.highSchoolGirl3+a.highSchoolGirl4+a.highSchoolGirl5+a.highSchoolGirl6),"+
					 "	sum(a.kindergartenMan3+a.kindergartenMan2+a.kindergartenMan1+a.primaryEducationMan1+"+
					 "	a.primaryEducationMan2+a.primaryEducationMan3+a.primaryEducationMan4+a.primaryEducationMan5+a.primaryEducationMan6+"+
					 "	a.highSchoolMan1+a.highSchoolMan2+a.highSchoolMan3+a.highSchoolMan4+a.highSchoolMan5+a.highSchoolMan6+"+
					 "	a.kindergartenGirl3+a.kindergartenGirl2+a.kindergartenGirl1+"+
					 "	a.primaryEducationGirl1+a.primaryEducationGirl2+a.primaryEducationGirl3+a.primaryEducationGirl4+a.primaryEducationGirl5+a.primaryEducationGirl6+"+
					 "	a.highSchoolGirl1+a.highSchoolGirl2+a.highSchoolGirl3+a.highSchoolGirl4+a.highSchoolGirl5+a.highSchoolGirl6)"+ 
					 "	from School a, SchoolInfo b"+ 
					 "	where a.percodeCode = b.percodeCode"+
					 "	      and b.districtId = "+district.getCode()+
					 "	group by 1"+
					 "	having sum(a.kindergartenMan3+a.kindergartenMan2+a.kindergartenMan1+a.primaryEducationMan1+"+
					 "	a.primaryEducationMan2+a.primaryEducationMan3+a.primaryEducationMan4+a.primaryEducationMan5+a.primaryEducationMan6+"+
					 "	a.highSchoolMan1+a.highSchoolMan2+a.highSchoolMan3+a.highSchoolMan4+a.highSchoolMan5+a.highSchoolMan6+"+
					 "	a.kindergartenGirl3+a.kindergartenGirl2+a.kindergartenGirl1+"+
					 "	a.primaryEducationGirl1+a.primaryEducationGirl2+a.primaryEducationGirl3+a.primaryEducationGirl4+a.primaryEducationGirl5+a.primaryEducationGirl6+"+
					 "	a.highSchoolGirl1+a.highSchoolGirl2+a.highSchoolGirl3+a.highSchoolGirl4+a.highSchoolGirl5+a.highSchoolGirl6) %S";
		sql = templateSQLScale(scale, sql);
		Integer[] ret = new Integer[3];
		Integer sumMan = 0;
		Integer sumGirl = 0;
		Integer sumAll = 0;
		List lst = em.createQuery(sql).getResultList();	
		for (Iterator iterator = lst.iterator(); iterator.hasNext();) {
			Object[] obj = (Object[]) iterator.next();
			sumMan += Integer.parseInt(obj[1].toString());
			sumGirl += Integer.parseInt(obj[2].toString());
			sumAll += Integer.parseInt(obj[3].toString());
		}
		ret[0] = sumMan;
		ret[1] = sumGirl;
		ret[2] = sumAll;
		return ret;
	}

	
	public Integer[] getByScaleSummary(DISTRICT district){
		String sql = "select   sum(a.kindergartenMan3+a.kindergartenMan2+a.kindergartenMan1+"+
					 "	a.primaryEducationMan1+a.primaryEducationMan2+a.primaryEducationMan3+a.primaryEducationMan4+a.primaryEducationMan5+a.primaryEducationMan6+"+    
					 "	a.highSchoolMan1+a.highSchoolMan2+a.highSchoolMan3+a.highSchoolMan4+a.highSchoolMan5+a.highSchoolMan6),"+
					 "	sum(a.kindergartenGirl3+a.kindergartenGirl2+a.kindergartenGirl1+"+     
					 "	a.primaryEducationGirl1+a.primaryEducationGirl2+a.primaryEducationGirl3+a.primaryEducationGirl4+a.primaryEducationGirl5+a.primaryEducationGirl6+"+     
					 "	a.highSchoolGirl1+a.highSchoolGirl2+a.highSchoolGirl3+a.highSchoolGirl4+a.highSchoolGirl5+a.highSchoolGirl6),"+
					 "	sum(a.kindergartenMan3+a.kindergartenMan2+a.kindergartenMan1+"+
					 "	a.primaryEducationMan1+a.primaryEducationMan2+a.primaryEducationMan3+a.primaryEducationMan4+a.primaryEducationMan5+a.primaryEducationMan6+"+    
					 "	a.highSchoolMan1+a.highSchoolMan2+a.highSchoolMan3+a.highSchoolMan4+a.highSchoolMan5+a.highSchoolMan6+"+
					 "	a.kindergartenGirl3+a.kindergartenGirl2+a.kindergartenGirl1+"+     
					 "	a.primaryEducationGirl1+a.primaryEducationGirl2+a.primaryEducationGirl3+a.primaryEducationGirl4+a.primaryEducationGirl5+a.primaryEducationGirl6+"+     
					 "	a.highSchoolGirl1+a.highSchoolGirl2+a.highSchoolGirl3+a.highSchoolGirl4+a.highSchoolGirl5+a.highSchoolGirl6)"+   
					 "	from School a, SchoolInfo b"+     
					 "	where a.percodeCode = b.percodeCode"+          
					 "	      and b.districtId = "+district.getCode();
		Object[] obj = (Object[])em.createQuery(sql).getResultList().get(0); 
		Integer[] ret = new Integer[3];
		ret[0] = Integer.parseInt(obj[0].toString());
		ret[1] = Integer.parseInt(obj[1].toString());
		ret[2] = Integer.parseInt(obj[2].toString());
		return ret;		
	}
	
	public String getByScaleXMLReport(){
		String sqlTemp = "select a.percodeCode,"+
					 " sum(a.kindergartenMan3+a.kindergartenMan2+a.kindergartenMan1+"+
					 "    a.primaryEducationMan1+a.primaryEducationMan2+a.primaryEducationMan3+a.primaryEducationMan4+"+
					 "    a.primaryEducationMan5+a.primaryEducationMan6+"+
					 "    a.highSchoolMan1+a.highSchoolMan2+a.highSchoolMan3+a.highSchoolMan4+a.highSchoolMan5+a.highSchoolMan6+"+
					 "    a.kindergartenGirl3+a.kindergartenGirl2+a.kindergartenGirl1+"+
					 "    a.primaryEducationGirl1+a.primaryEducationGirl2+a.primaryEducationGirl3+a.primaryEducationGirl4+"+
					 "    a.primaryEducationGirl5+a.primaryEducationGirl6+"+
					 "    a.highSchoolGirl1+a.highSchoolGirl2+a.highSchoolGirl3+a.highSchoolGirl4+a.highSchoolGirl5+a.highSchoolGirl6)"+ 
					 " from School a, SchoolInfo b, District c"+
					 " where a.percodeCode = b.percodeCode"+
					 "	     and b.districtId = c.districtId"+  
					 " group by 1"+
					 " having  sum(a.kindergartenMan3+a.kindergartenMan2+a.kindergartenMan1+"+
					 "         a.primaryEducationMan1+a.primaryEducationMan2+a.primaryEducationMan3+a.primaryEducationMan4+"+
					 "         a.primaryEducationMan5+a.primaryEducationMan6+"+
					 "         a.highSchoolMan1+a.highSchoolMan2+a.highSchoolMan3+a.highSchoolMan4+a.highSchoolMan5+a.highSchoolMan6+"+
					 "         a.kindergartenGirl3+a.kindergartenGirl2+a.kindergartenGirl1+"+
					 "         a.primaryEducationGirl1+a.primaryEducationGirl2+a.primaryEducationGirl3+a.primaryEducationGirl4+"+
					 "         a.primaryEducationGirl5+a.primaryEducationGirl6+"+
					 "         a.highSchoolGirl1+a.highSchoolGirl2+a.highSchoolGirl3+a.highSchoolGirl4+a.highSchoolGirl5+a.highSchoolGirl6) %s";
        try {
            /////////////////////////////
            //Creating an empty XML Document

            //We need a Document
            DocumentBuilderFactory dbfac = DocumentBuilderFactory.newInstance();
            DocumentBuilder docBuilder = dbfac.newDocumentBuilder();
            Document doc = docBuilder.newDocument();

            ////////////////////////
            //Creating the XML tree
            
            
            //create the root element and add it to the document
            Element root = doc.createElement("pie");
            doc.appendChild(root);

            //create child element, add an attribute, and add to root
            
//			List<Object[]> lst = em.createQuery(sql).getResultList();
//			for (Object[] obj : lst) {
//	            Element child = doc.createElement("slice");
//	            child.setAttribute("title", obj[0].toString());
//	            Text text = doc.createTextNode(obj[1].toString());
//	            child.appendChild(text);
//	            root.appendChild(child);
//			}
            
            for(SCALE scale:EnumSet.allOf(SCALE.class)){
            	if(scale.isSubScale()) continue;
            	String sql = templateSQLScale(scale, sqlTemp);
            	List<Object[]> lst = em.createQuery(sql).getResultList();
            	Element child = doc.createElement("slice");
            	child.setAttribute("title", scale.getMessageDisplay());
            	Text text = doc.createTextNode(sum(lst)+"");
	            child.appendChild(text);
	            root.appendChild(child);
            }

            
            /////////////////
            //Output the XML

            //set up a transformer
            TransformerFactory transfac = TransformerFactory.newInstance();
            Transformer trans = transfac.newTransformer();

            //create string from xml tree
            StringWriter sw = new StringWriter();
            StreamResult result = new StreamResult(sw);
            DOMSource source = new DOMSource(doc);
            trans.transform(source, result);
            String xmlString = sw.toString();

            return xmlString.substring(xmlString.indexOf("?>") + 2).replaceAll("\"", "'");
        } catch (Exception e) {
            System.out.println(e);
        }		
		return null;		
	}
	
	private Integer sum(List<Object[]> lst){
		Integer ret = 0;
		for (Object[] objects : lst) {
			ret += Integer.parseInt(objects[1].toString());
		}
		return ret;
	}

	public static void main(String[] args) {
		ReportByScale obj = new ReportByScale();
		System.out.println(obj.getByScaleXMLReport());
	}
}
